/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package br.uniceub.fsw.agenda.model.dao;

import br.uniceub.fsw.agenda.exception.BusinessException;
import br.uniceub.fsw.agenda.exception.DatabaseException;
import br.uniceub.fsw.agenda.exception.UniqueConstraintException;
import br.uniceub.fsw.agenda.model.vo.UFVO;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 05/04/2008
 * @author Rodrigo Uchôa - rodrigo.uchoa@yahoo.com
 *
 */
public class UFDAO extends GenericDAO {
    
    public UFDAO(Connection con) {
        super(con);
    }   
    
    /**
     * Buscar objeto Estado pela sua sigla
     * @param sgUf sigla do estado que se deseja buscar
     * @return objeto UF
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public UFVO findBySigla(String sgUf) throws DatabaseException {
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM TB_UF WHERE SG_UF = ?");
        
        try {
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, sgUf);
            
            ResultSet rs = st.executeQuery();

            UFVO estado = new UFVO();
            
            while (rs.next()) {
                estado.setSgUf(rs.getString("SG_UF"));
                estado.setNoUf(rs.getString("NO_UF"));
            }
            
            st.close();
            rs.close();
            
            return estado;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOUF.findBySigla()", ex);
        }
    }

    /**
     * Buscar Lista de Estados pela sua sigla ou parte dela (primary key)
     * @param sgUf sigla do estado que se deseja buscar
     * @return lista de objetos UF
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public List<UFVO> findAllBySigla(String sgUf) throws DatabaseException {
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM TB_UF WHERE SG_UF LIKE ? ORDER BY SG_UF");
        
        try {
            PreparedStatement st = con.prepareStatement(query.toString());
            String filtro = "%" + sgUf + "%";
            st.setString(1, filtro);
            
            List<UFVO> listaRegistros = new ArrayList<UFVO>();
            
            ResultSet rs = st.executeQuery();

            while (rs.next()) {
                UFVO estado = new UFVO();
                estado.setSgUf(rs.getString("SG_UF"));
                estado.setNoUf(rs.getString("NO_UF"));
                
                listaRegistros.add(estado);
            }
            st.close();
            rs.close();
            return listaRegistros;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOUF.findAllBySigla()", ex);
        }
    }

    /**
     * Buscar lista de estados por parte de seu nome
     * @param noUf parte do nome dos estados que se deseja buscar
     * @return List de objetos UF
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public List<UFVO> findAllByNome(String noUf) throws DatabaseException {
        StringBuilder query = new StringBuilder();
        query.append("SELECT * FROM TB_UF WHERE NO_UF LIKE ? ORDER BY NO_UF");
        
        try {
            PreparedStatement st = con.prepareStatement(query.toString());
            String filtro = "%" + noUf + "%";
            st.setString(1, filtro);
            
            List<UFVO> listaRegistros = new ArrayList<UFVO>();
            
            ResultSet rs = st.executeQuery();

            while (rs.next()) {
                UFVO estado = new UFVO();
                estado.setSgUf(rs.getString("SG_UF"));
                estado.setNoUf(rs.getString("NO_UF"));
                
                listaRegistros.add(estado);
            }
            st.close();
            rs.close();
            return listaRegistros;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOUF.findAllByNome()", ex);
        }
    }

    /**
     * Buscar lista de estados
     * @return List de objetos UF
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public List<UFVO> findAll() throws BusinessException, DatabaseException {
        //montar string de consulta
        StringBuilder query = new StringBuilder();
        query.append("SELECT sg_uf, no_uf ");
        query.append("FROM TB_UF ");
        query.append("ORDER BY sg_uf");
        
        //lista que vai armazenar os objetos UF da consulta
        List<UFVO> resultado = new ArrayList<UFVO>();
        
        try {
            
            //objeto con é herdado da super classe
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query.toString());
            while (rs.next()) {
                UFVO uf = new UFVO();
                uf.setSgUf(rs.getString("sg_uf"));
                uf.setNoUf(rs.getString("no_uf"));
                
                resultado.add(uf);
            }
            
            st.close();
            rs.close();
            
            return resultado;            
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DatabaseException("Erro ao buscar UF's", ex);            
        }        
    }

    /**
     * Faz o insert de um novo estado e devolve a o id recem gerado
     * @param UF estado que se deseja inserir
     * @return ID auto-increment gerado para esse registro
     * @throws br.uniceub.fsw.agenda.exception.BusinessException
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public Integer insert(UFVO estado) throws BusinessException, DatabaseException {        
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO TB_UF(SG_UF, NO_UF) ");
        query.append("VALUES (?,?)");             
        
        //retornar id gerado
        Integer generatedId = null;
        
        try {           
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, estado.getSgUf());
            st.setString(2, estado.getNoUf());
            
            st.executeUpdate();
            
            //pegar id gerado
            ResultSet rs = st.getGeneratedKeys();
            while (rs.next()) {
                generatedId = new Integer(rs.getString(1));                
            }                    
            
            return generatedId;            
        } catch (SQLException ex) {            
            ex.printStackTrace();
            if (ex.getErrorCode() == 1062) {
                throw new UniqueConstraintException(ex.getMessage(), ex);                
            }
            throw new DatabaseException("Erro no DAOUF.insert()", ex);            
        }      
    }
    
    /**
     * Faz o update de um estado
     * @param UF estado que se deseja alterar
     * @throws br.uniceub.fsw.agenda.exception.BusinessException
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public void update(UFVO estado) throws BusinessException, DatabaseException {        
        StringBuilder query = new StringBuilder();
        query.append("UPDATE TB_UF SET NO_UF = ? ");
        query.append("WHERE SG_UF = ?");             
        
        try {           
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, estado.getNoUf());
            st.setString(2, estado.getSgUf());
            
            st.executeUpdate();
        } catch (SQLException ex) {            
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOUF.update()", ex);
        }      
    }
    
    /**
     * Faz o delete de um estado
     * @param UF estado que se deseja excluir
     * @throws br.uniceub.fsw.agenda.exception.BusinessException
     * @throws br.uniceub.fsw.agenda.exception.DatabaseException
     */
    public void delete(UFVO estado) throws BusinessException, DatabaseException {        
        StringBuilder query = new StringBuilder();
        query.append("DELETE FROM TB_UF ");
        query.append("WHERE SG_UF = ?");             
        
        try {           
            PreparedStatement st = con.prepareStatement(query.toString());
            st.setString(1, estado.getSgUf());
            
            st.executeUpdate();
        } catch (SQLException ex) {            
            ex.printStackTrace();
            throw new DatabaseException("Erro no DAOUF.delete()", ex);            
        }      
    }
    
}
